{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-28T06:59:24.586762Z",
     "start_time": "2024-08-28T06:59:24.338382Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "folder_sources = './数据源/'\n",
    "folder_result = './处理结果/'\n",
    "\n",
    "folder_sources_AMZReport = folder_sources + \"AMZ结算报告/\"\n",
    "\n",
    "\n",
    "def create_folder(path: str):\n",
    "    if not os.path.exists(path):\n",
    "        os.mkdir(path)\n",
    "        print(f'{path} 文件夹创建成功！')\n",
    "    else:\n",
    "        print(f'{path} 文件夹已经存在！')\n",
    "\n",
    "\n",
    "create_folder(folder_sources)\n",
    "create_folder(folder_sources_AMZReport)\n",
    "create_folder(folder_result)"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./数据源/ 文件夹已经存在！\n",
      "./数据源/AMZ结算报告/ 文件夹已经存在！\n",
      "./处理结果/ 文件夹已经存在！\n"
     ]
    }
   ],
   "execution_count": 2
  },
  {
   "cell_type": "markdown",
   "id": "1ef04149a8ad514b",
   "metadata": {},
   "source": [
    "### 1.读取亚马逊结算报告，按照站点合并报告\n",
    "    各个站点规则：\n",
    "    日本：表头在第8行\n",
    "    美国，加拿大：表头在第8行\n",
    "    阿联酋：表头在第7行\n",
    "    欧洲：表头在第8行"
   ]
  },
  {
   "cell_type": "code",
   "id": "a4d01451bb0b1277",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-28T06:59:24.736054Z",
     "start_time": "2024-08-28T06:59:24.588604Z"
    }
   },
   "source": [
    "data_list_us = []\n",
    "data_list_jp = []\n",
    "data_list_eu = []\n",
    "data_list_ae = []\n",
    "data_list_mx = []\n",
    "\n",
    "eu_header = {\n",
    "    'uk': \"date/time,settlement id,type,order id,sku,description,quantity,marketplace,fulfilment,order city,order state,order postal,tax collection model,product sales,product sales tax,postage credits,shipping credits tax,gift wrap credits,giftwrap credits tax,promotional rebates,promotional rebates tax,marketplace withheld tax,selling fees,fba fees,other transaction fees,other,total\".split(\n",
    "        ','),\n",
    "    'pl': \"data/godzina,identyfikator rozliczenia,typ,identyfikator zamówienia,sku,opis,ilość,rynek,realizacja,miejscowość zamówienia,stan zamówienia,przekaz pocztowy,sprzedaż produktów,noty kredytowe za wysyłkę,środki na pokrycie pakowania na prezent,rabaty promocyjne,pobrany podatek od sprzedaży,Marketplace Facilitator Tax,opłaty za sprzedaż,opłaty za fba,inne opłaty transakcyjne,inne,suma\".split(\n",
    "        ','),\n",
    "    'nl': \"datum/tijd,schikkings-ID,type,bestelnummer,sku,beschrijving,aantal,marketplace,fulfillment,bestelling stad,status bestelling,bestelling per post,verkoop van producten,Verzendtegoeden,kredietpunten cadeauverpakking,promotiekortingen,geïnde omzetbelasting,Belasting voor marketplace-facilitator,verkoopkosten,fba-vergoedingen,overige transactiekosten,overige,totaal\".split(\n",
    "        ','),\n",
    "    'it': \"Data/Ora:,Numero pagamento,Tipo,Numero ordine,SKU,Descrizione,Quantità,Marketplace,Gestione,Città di provenienza dell'ordine,Provincia di provenienza dell'ordine,CAP dell'ordine,modello di riscossione delle imposte,Vendite,imposta sulle vendite dei prodotti,Accrediti per le spedizioni,imposta accrediti per le spedizioni,Accrediti per confezioni regalo,imposta sui crediti confezione regalo,Sconti promozionali,imposta sugli sconti promozionali,trattenuta IVA del marketplace,Commissioni di vendita,Costi del servizio Logistica di Amazon,Altri costi relativi alle transazioni,Altro,totale\".split(\n",
    "        ','),\n",
    "    'fr': \"date/heure,numéro de versement,type,numéro de la commande,sku,description,quantité,Marketplace,traitement,ville d'où provient la commande,Région d'où provient la commande,code postal de la commande,Modèle de perception des taxes,ventes de produits,Taxes sur la vente des produits,crédits d'expédition,taxe sur les crédits d’expédition,crédits sur l'emballage cadeau,Taxes sur les crédits cadeaux,Rabais promotionnels,Taxes sur les remises promotionnelles,Taxes retenues sur le site de vente,frais de vente,Frais Expédié par Amazon,autres frais de transaction,autre,total\".split(\n",
    "        ','),\n",
    "    'es': \"fecha y hora,identificador de pago,tipo,número de pedido,sku,descripción,cantidad,web de Amazon,gestión logística,ciudad de procedencia del pedido,comunidad autónoma de procedencia del pedido,código postal de procedencia del pedido,Formulario de recaudación de impuestos,ventas de productos,impuesto de ventas de productos,abonos de envío,impuestos por abonos de envío,abonos de envoltorio para regalo,giftwrap credits tax,devoluciones promocionales,promotional rebates tax,impuesto retenido en el sitio web,tarifas de venta,tarifas de Logística de Amazon,tarifas de otras transacciones,otro,total\".split(\n",
    "        ','),\n",
    "    'de': \"Datum/Uhrzeit,Abrechnungsnummer,Typ,Bestellnummer,SKU,Beschreibung,Menge,Marketplace,Versand,Ort der Bestellung,Bundesland,Postleitzahl,Steuererhebungsmodell,Umsätze,Produktumsatzsteuer,Gutschrift für Versandkosten,Steuer auf Versandgutschrift,Gutschrift für Geschenkverpackung,Steuer auf Geschenkverpackungsgutschriften,Rabatte aus Werbeaktionen,Steuer auf Aktionsrabatte,Einbehaltene Steuer auf Marketplace,Verkaufsgebühren,Gebühren zu Versand durch Amazon,Andere Transaktionsgebühren,Andere,Gesamt\".split(\n",
    "        ',')\n",
    "}\n",
    "\n",
    "for f_name in os.listdir(folder_sources_AMZReport):\n",
    "    if f_name.endswith(\".csv\"):\n",
    "\n",
    "        # if 'AE' in f_name:\n",
    "        #     header_index = 5\n",
    "        # else:\n",
    "        #     header_index = 6\n",
    "        # df_excel.columns = header\n",
    "\n",
    "        print(folder_sources_AMZReport + f_name)\n",
    "        df_csv = pd.read_csv(folder_sources_AMZReport + f_name)\n",
    "        # temp = df_csv.iloc[0:10]\n",
    "        # print(df_csv.columns)\n",
    "        # df_csv = df_csv.drop(labels=[0], axis=0).reset_index(drop=True)\n",
    "\n",
    "        # df_excel.drop(np.arange(5), axis=0)\n",
    "        # print(f'{f_name} 一共有 {len(df_excel)}条数据')\n",
    "        # print(df_excel)\n",
    "\n",
    "        if 'AE' in f_name:\n",
    "            data_list_ae.append(df_csv)\n",
    "        elif 'JP' in f_name:\n",
    "            data_list_jp.append(df_csv)\n",
    "        elif 'EU' in f_name:\n",
    "            if 'DE' in f_name or 'ES' in f_name or 'FR' in f_name or 'IT' in f_name:\n",
    "                df_csv.columns = eu_header['uk']\n",
    "            elif 'NL' in f_name or 'PL' in f_name:\n",
    "                tax_header = ['tax collection model', 'product sales tax', 'shipping credits tax', 'giftwrap credits tax']\n",
    "                # temp_header = eu_header['uk']\n",
    "                # for h in remove_header:\n",
    "                #     temp_header.pop(temp_header.index(h))\n",
    "                # df_csv.columns = temp_header\n",
    "                \n",
    "                if not pd.Series(df_csv.columns).isin(tax_header).any():\n",
    "                    df_csv.insert(loc=12, column='tax collection model',value=np.nan)\n",
    "                    df_csv.insert(loc=14, column='product sales tax',value=np.nan)\n",
    "                    df_csv.insert(loc=16, column='shipping credits tax',value=np.nan)\n",
    "                    df_csv.insert(loc=18, column='giftwrap credits tax',value=np.nan)\n",
    "                    df_csv.columns = eu_header['uk']\n",
    "            \n",
    "            \n",
    "            # 处理金额数据格式\n",
    "            swap_columns = \"product sales,product sales tax,postage credits,shipping credits tax,gift wrap credits,giftwrap credits tax,promotional rebates,promotional rebates tax,marketplace withheld tax,selling fees,fba fees,other transaction fees,other,total\".split(',')\n",
    "            for column in swap_columns:\n",
    "                # print(f\"{column},{df_csv[column].dtype}\")\n",
    "                if df_csv[column].dtype == 'object':\n",
    "                    df_csv[column] = df_csv[column].str.replace(',','.').astype(float)\n",
    "            data_list_eu.append(df_csv)\n",
    "        elif 'US' in f_name:\n",
    "            swap_columns = \"product sales,product sales tax,shipping credits,shipping credits tax,gift wrap credits,giftwrap credits tax,Regulatory Fee,Tax On Regulatory Fee,promotional rebates,promotional rebates tax,marketplace withheld tax,selling fees,fba fees,other transaction fees,other,total\".split(',')\n",
    "            for column in swap_columns:\n",
    "                # print(f\"{column},{df_csv[column].dtype}\")\n",
    "                if df_csv[column].dtype == 'object':\n",
    "                    df_csv[column] = df_csv[column].str.replace(',','').astype(float)\n",
    "            data_list_us.append(df_csv)\n",
    "        elif 'MX' in f_name:\n",
    "            data_list_mx.append(df_csv)\n",
    "\n",
    "        df_csv.insert(0, column='Store', value=f_name.split('.')[0])\n",
    "        # print(df_excel.columns)\n",
    "\n",
    "        # print(df_excel.iloc[0:2])\n",
    "        # print(f'{folder_sources}/New-{f_name}')\n",
    "pd.concat(data_list_ae).to_csv(folder_result + 'NEW-AE.csv', index=False)\n",
    "pd.concat(data_list_jp).to_csv(folder_result + 'NEW-JP.csv', index=False)\n",
    "pd.concat(data_list_eu).to_csv(folder_result + 'NEW-EU.csv', index=False)\n",
    "pd.concat(data_list_us).to_csv(folder_result + 'NEW-US.csv', index=False)\n",
    "pd.concat(data_list_mx).to_csv(folder_result + 'NEW-MX.csv', index=False)\n",
    "\n",
    "\n",
    "\n",
    "# df_csv.to_csv(f'{folder_sources}/New-{f_name}', index=False)\n",
    "\n",
    "# store_name = f_name.split('-')[0]\n",
    "# # print(f'店铺名称：{store_name}')\n",
    "# for sheet_name, df_data in df_excel.items():\n",
    "#     df_data.insert(0, column='店铺', value=store_name)\n",
    "# \n",
    "#     if sheet_name in data_list.keys():\n",
    "#         data_list[sheet_name] = pd.concat([data_list[sheet_name], df_data], ignore_index=True)\n",
    "#         # print('---------------')\n",
    "#         # print('已经存在了，合并数据')\n",
    "#     else:\n",
    "#         data_list[sheet_name] = df_data\n",
    "\n",
    "# print(f'表名：{sheet_name},行数：{df_data.count()}')\n",
    "# \n",
    "# for sheet_name, data in data_list.items():\n",
    "#     data.to_csv(f\"{folder_result}/1.{sheet_name}.csv\", index=False)\n",
    "# \n",
    "#     print(f'合并 {sheet_name} 数据完成，数据量：{len(data)}条')"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./数据源/AMZ结算报告/SC-US.csv\n",
      "./数据源/AMZ结算报告/YT-EU-UK.csv\n",
      "./数据源/AMZ结算报告/SZB-JP.csv\n",
      "./数据源/AMZ结算报告/XWK-JP.csv\n",
      "./数据源/AMZ结算报告/YT-CA.csv\n",
      "./数据源/AMZ结算报告/SZB-US.csv\n",
      "./数据源/AMZ结算报告/SC-JP.csv\n",
      "./数据源/AMZ结算报告/YT-EU-ES.csv\n",
      "./数据源/AMZ结算报告/YT-EU-IT.csv\n",
      "./数据源/AMZ结算报告/YT-EU-DE.csv\n",
      "./数据源/AMZ结算报告/SZB-AE.csv\n",
      "./数据源/AMZ结算报告/XWK-US.csv\n",
      "./数据源/AMZ结算报告/XWK-MX.csv\n",
      "./数据源/AMZ结算报告/SZB-CA.csv\n",
      "./数据源/AMZ结算报告/YT-EU-PL.csv\n",
      "./数据源/AMZ结算报告/YT-EU-FR.csv\n",
      "./数据源/AMZ结算报告/XWK-CA.csv\n",
      "./数据源/AMZ结算报告/YT-JP.csv\n",
      "./数据源/AMZ结算报告/XWK-EU-DE.csv\n",
      "./数据源/AMZ结算报告/YT-US.csv\n",
      "./数据源/AMZ结算报告/YT-EU-NL.csv\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "      Store                   date/time  settlement id    type  \\\n",
       "0    SZB-AE  30 Jun 2024 8:13:52 PM UTC    22575404662   Order   \n",
       "1    SZB-AE  1 Jul 2024 10:06:48 AM UTC    22589264812   Order   \n",
       "2    SZB-AE   1 Jul 2024 3:05:55 PM UTC    22589264812   Order   \n",
       "3    SZB-AE   1 Jul 2024 3:06:15 PM UTC    22589264812   Order   \n",
       "4    SZB-AE   1 Jul 2024 5:01:34 PM UTC    22589264812   Order   \n",
       "..      ...                         ...            ...     ...   \n",
       "164  SZB-AE  30 Jul 2024 9:31:44 PM UTC    22765799162   Order   \n",
       "165  SZB-AE  30 Jul 2024 9:50:03 PM UTC    22765799162   Order   \n",
       "166  SZB-AE  31 Jul 2024 9:31:41 AM UTC    22781231002  Refund   \n",
       "167  SZB-AE  31 Jul 2024 4:29:53 PM UTC    22781231002   Order   \n",
       "168  SZB-AE  31 Jul 2024 6:58:21 PM UTC    22781231002   Order   \n",
       "\n",
       "                order id           sku  \\\n",
       "0    408-2907130-8349903  G8-NOGZ-0KSP   \n",
       "1    408-8541422-5114760  G8-NOGZ-0KSP   \n",
       "2    406-9449183-1644327  G8-NOGZ-0KSP   \n",
       "3    403-4181186-9776327  G8-NOGZ-0KSP   \n",
       "4    403-1859226-0374736  G8-NOGZ-0KSP   \n",
       "..                   ...           ...   \n",
       "164  404-1610794-1167566  G8-NOGZ-0KSP   \n",
       "165  403-9582092-6586726  G8-NOGZ-0KSP   \n",
       "166  402-6112560-0357946  21-I1N0-CUS8   \n",
       "167  408-7846254-1141160  G8-NOGZ-0KSP   \n",
       "168  404-5310538-8062721  G8-NOGZ-0KSP   \n",
       "\n",
       "                                           description  quantity marketplace  \\\n",
       "0    Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "1    Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "2    Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "3    Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       2.0   amazon.ae   \n",
       "4    Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "..                                                 ...       ...         ...   \n",
       "164  Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "165  Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "166  Epoxy Resin Beginner's Kit, 36oz/1064ml Non To...       1.0   amazon.ae   \n",
       "167  Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "168  Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...       1.0   amazon.ae   \n",
       "\n",
       "    fulfillment  ... order state  order postal  product sales  \\\n",
       "0        Amazon  ...         NaN           NaN          55.99   \n",
       "1        Amazon  ...         NaN           NaN          44.39   \n",
       "2        Amazon  ...         NaN           NaN          44.39   \n",
       "3        Amazon  ...         NaN           NaN          88.78   \n",
       "4        Amazon  ...         NaN           NaN          55.99   \n",
       "..          ...  ...         ...           ...            ...   \n",
       "164      Amazon  ...         NaN           NaN          55.99   \n",
       "165      Amazon  ...         NaN           NaN          55.99   \n",
       "166      Amazon  ...         NaN           NaN         -74.00   \n",
       "167      Amazon  ...         NaN           NaN          55.99   \n",
       "168      Amazon  ...         NaN           NaN          55.99   \n",
       "\n",
       "     shipping credits  promotional rebates  selling fees  fba fees  \\\n",
       "0                0.00                 0.00         -5.60     -10.5   \n",
       "1                0.00                 0.00         -4.44     -10.5   \n",
       "2                0.00                 0.00         -4.44     -10.5   \n",
       "3                0.00                 0.00         -8.88     -21.0   \n",
       "4                3.33                -3.33         -5.60     -10.5   \n",
       "..                ...                  ...           ...       ...   \n",
       "164              0.00               -11.20         -4.48     -10.5   \n",
       "165              1.80               -13.00         -4.48     -10.5   \n",
       "166             -5.00                 5.00          5.92       0.0   \n",
       "167              3.00               -14.20         -4.48     -10.5   \n",
       "168              4.50                 0.00         -5.60     -15.0   \n",
       "\n",
       "     other transaction fees  other  total  \n",
       "0                       0.0    0.0  39.89  \n",
       "1                       0.0    0.0  29.45  \n",
       "2                       0.0    0.0  29.45  \n",
       "3                       0.0    0.0  58.90  \n",
       "4                       0.0    0.0  39.89  \n",
       "..                      ...    ...    ...  \n",
       "164                     0.0    0.0  29.81  \n",
       "165                     0.0    0.0  29.81  \n",
       "166                     0.0    0.0 -68.08  \n",
       "167                     0.0    0.0  29.81  \n",
       "168                     0.0    0.0  39.89  \n",
       "\n",
       "[169 rows x 21 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Store</th>\n",
       "      <th>date/time</th>\n",
       "      <th>settlement id</th>\n",
       "      <th>type</th>\n",
       "      <th>order id</th>\n",
       "      <th>sku</th>\n",
       "      <th>description</th>\n",
       "      <th>quantity</th>\n",
       "      <th>marketplace</th>\n",
       "      <th>fulfillment</th>\n",
       "      <th>...</th>\n",
       "      <th>order state</th>\n",
       "      <th>order postal</th>\n",
       "      <th>product sales</th>\n",
       "      <th>shipping credits</th>\n",
       "      <th>promotional rebates</th>\n",
       "      <th>selling fees</th>\n",
       "      <th>fba fees</th>\n",
       "      <th>other transaction fees</th>\n",
       "      <th>other</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>30 Jun 2024 8:13:52 PM UTC</td>\n",
       "      <td>22575404662</td>\n",
       "      <td>Order</td>\n",
       "      <td>408-2907130-8349903</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-5.60</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>1 Jul 2024 10:06:48 AM UTC</td>\n",
       "      <td>22589264812</td>\n",
       "      <td>Order</td>\n",
       "      <td>408-8541422-5114760</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.39</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-4.44</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>1 Jul 2024 3:05:55 PM UTC</td>\n",
       "      <td>22589264812</td>\n",
       "      <td>Order</td>\n",
       "      <td>406-9449183-1644327</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.39</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-4.44</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>1 Jul 2024 3:06:15 PM UTC</td>\n",
       "      <td>22589264812</td>\n",
       "      <td>Order</td>\n",
       "      <td>403-4181186-9776327</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88.78</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-8.88</td>\n",
       "      <td>-21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>58.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>1 Jul 2024 5:01:34 PM UTC</td>\n",
       "      <td>22589264812</td>\n",
       "      <td>Order</td>\n",
       "      <td>403-1859226-0374736</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>3.33</td>\n",
       "      <td>-3.33</td>\n",
       "      <td>-5.60</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>30 Jul 2024 9:31:44 PM UTC</td>\n",
       "      <td>22765799162</td>\n",
       "      <td>Order</td>\n",
       "      <td>404-1610794-1167566</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-11.20</td>\n",
       "      <td>-4.48</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>30 Jul 2024 9:50:03 PM UTC</td>\n",
       "      <td>22765799162</td>\n",
       "      <td>Order</td>\n",
       "      <td>403-9582092-6586726</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>1.80</td>\n",
       "      <td>-13.00</td>\n",
       "      <td>-4.48</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>31 Jul 2024 9:31:41 AM UTC</td>\n",
       "      <td>22781231002</td>\n",
       "      <td>Refund</td>\n",
       "      <td>402-6112560-0357946</td>\n",
       "      <td>21-I1N0-CUS8</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 36oz/1064ml Non To...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.00</td>\n",
       "      <td>-5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.92</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-68.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>31 Jul 2024 4:29:53 PM UTC</td>\n",
       "      <td>22781231002</td>\n",
       "      <td>Order</td>\n",
       "      <td>408-7846254-1141160</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>3.00</td>\n",
       "      <td>-14.20</td>\n",
       "      <td>-4.48</td>\n",
       "      <td>-10.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>SZB-AE</td>\n",
       "      <td>31 Jul 2024 6:58:21 PM UTC</td>\n",
       "      <td>22781231002</td>\n",
       "      <td>Order</td>\n",
       "      <td>404-5310538-8062721</td>\n",
       "      <td>G8-NOGZ-0KSP</td>\n",
       "      <td>Epoxy Resin Beginner's Kit, 18oz/532ml Non Tox...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>amazon.ae</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.99</td>\n",
       "      <td>4.50</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-5.60</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>169 rows × 21 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 3
  },
  {
   "cell_type": "code",
   "id": "cd7d8a73cc187993",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-28T06:59:24.773443Z",
     "start_time": "2024-08-28T06:59:24.736708Z"
    }
   },
   "source": [
    "list_us = pd.read_csv(folder_result + 'NEW-US.csv')\n",
    "\n",
    "# list_us['other'].astype(float)\n",
    "# list_us['total'].astype(float)\n",
    "# print(list_us.dtypes)\n",
    "# print(list_us)\n",
    "list_pivot = pd.pivot_table(list_us,index=['Store','type','description'],values=['quantity']+\"product sales,product sales tax,shipping credits,shipping credits tax,gift wrap credits,giftwrap credits tax,Regulatory Fee,Tax On Regulatory Fee,promotional rebates,promotional rebates tax,marketplace withheld tax,selling fees,fba fees,other transaction fees,other,total\".split(','),aggfunc='sum',margins=True,margins_name='合计')\n",
    "\n",
    "list_pivot\n"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                      Regulatory Fee  \\\n",
       "Store type        description                                                          \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                    0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...               0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                  0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                 0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                    0   \n",
       "...                                                                              ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...               0   \n",
       "      Service Fee Cost of Advertising                                              0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...               0   \n",
       "                  Subscription                                                     0   \n",
       "合计                                                                                 0   \n",
       "\n",
       "                                                                      Tax On Regulatory Fee  \\\n",
       "Store type        description                                                                 \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                           0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                      0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                         0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                        0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                           0   \n",
       "...                                                                                     ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                      0   \n",
       "      Service Fee Cost of Advertising                                                     0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                      0   \n",
       "                  Subscription                                                            0   \n",
       "合计                                                                                        0   \n",
       "\n",
       "                                                                      fba fees  \\\n",
       "Store type        description                                                    \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return           0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...      0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse         0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment        0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                           0.00   \n",
       "...                                                                        ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...      0.00   \n",
       "      Service Fee Cost of Advertising                                     0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...      0.00   \n",
       "                  Subscription                                            0.00   \n",
       "合计                                                                   -62974.91   \n",
       "\n",
       "                                                                      gift wrap credits  \\\n",
       "Store type        description                                                             \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                       0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                  0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                     0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                    0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                       0   \n",
       "...                                                                                 ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                  0   \n",
       "      Service Fee Cost of Advertising                                                 0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                  0   \n",
       "                  Subscription                                                        0   \n",
       "合计                                                                                    0   \n",
       "\n",
       "                                                                      giftwrap credits tax  \\\n",
       "Store type        description                                                                \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                          0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                     0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                        0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                       0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                          0   \n",
       "...                                                                                    ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                     0   \n",
       "      Service Fee Cost of Advertising                                                    0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                     0   \n",
       "                  Subscription                                                           0   \n",
       "合计                                                                                       0   \n",
       "\n",
       "                                                                      marketplace withheld tax  \\\n",
       "Store type        description                                                                    \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                           0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                      0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                         0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                        0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                           0.00   \n",
       "...                                                                                        ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                      2.47   \n",
       "      Service Fee Cost of Advertising                                                     0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                      0.00   \n",
       "                  Subscription                                                            0.00   \n",
       "合计                                                                                    -4698.76   \n",
       "\n",
       "                                                                        other  \\\n",
       "Store type        description                                                   \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return         52.39   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...    38.28   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse       97.70   \n",
       "                  FBA Inventory Reimbursement - General Adjustment     -15.20   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                       5036.35   \n",
       "...                                                                       ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...     0.00   \n",
       "      Service Fee Cost of Advertising                                    0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...     0.00   \n",
       "                  Subscription                                         -12.85   \n",
       "合计                                                                    1396.16   \n",
       "\n",
       "                                                                      other transaction fees  \\\n",
       "Store type        description                                                                  \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                         0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                    0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                       0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                      0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                         0.00   \n",
       "...                                                                                      ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                    0.00   \n",
       "      Service Fee Cost of Advertising                                                -783.14   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                   -6.00   \n",
       "                  Subscription                                                          0.00   \n",
       "合计                                                                                   -300.04   \n",
       "\n",
       "                                                                      product sales  \\\n",
       "Store type        description                                                         \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...           0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse              0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment             0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                0.00   \n",
       "...                                                                             ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...         -29.99   \n",
       "      Service Fee Cost of Advertising                                          0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...           0.00   \n",
       "                  Subscription                                                 0.00   \n",
       "合计                                                                         70599.24   \n",
       "\n",
       "                                                                      product sales tax  \\\n",
       "Store type        description                                                             \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                    0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...               0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                  0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                 0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                    0.00   \n",
       "...                                                                                 ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...              -2.47   \n",
       "      Service Fee Cost of Advertising                                              0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...               0.00   \n",
       "                  Subscription                                                     0.00   \n",
       "合计                                                                              4688.43   \n",
       "\n",
       "                                                                      promotional rebates  \\\n",
       "Store type        description                                                               \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                      0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                 0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                    0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                   0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                      0.00   \n",
       "...                                                                                   ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                 0.00   \n",
       "      Service Fee Cost of Advertising                                                0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                 0.00   \n",
       "                  Subscription                                                       0.00   \n",
       "合计                                                                               -4027.23   \n",
       "\n",
       "                                                                      promotional rebates tax  \\\n",
       "Store type        description                                                                   \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                             0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                        0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                           0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                          0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                             0   \n",
       "...                                                                                       ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                        0   \n",
       "      Service Fee Cost of Advertising                                                       0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                        0   \n",
       "                  Subscription                                                              0   \n",
       "合计                                                                                          0   \n",
       "\n",
       "                                                                      quantity  \\\n",
       "Store type        description                                                    \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return            4.0   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...       2.0   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse          6.0   \n",
       "                  FBA Inventory Reimbursement - General Adjustment         1.0   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                            0.0   \n",
       "...                                                                        ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...       1.0   \n",
       "      Service Fee Cost of Advertising                                      0.0   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...      10.0   \n",
       "                  Subscription                                             0.0   \n",
       "合计                                                                      5869.0   \n",
       "\n",
       "                                                                      selling fees  \\\n",
       "Store type        description                                                        \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return               0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...          0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse             0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment            0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                               0.00   \n",
       "...                                                                            ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...          3.60   \n",
       "      Service Fee Cost of Advertising                                         0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...          0.00   \n",
       "                  Subscription                                                0.00   \n",
       "合计                                                                       -10065.98   \n",
       "\n",
       "                                                                      shipping credits  \\\n",
       "Store type        description                                                            \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                   0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...              0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                 0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                   0.00   \n",
       "...                                                                                ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...              0.00   \n",
       "      Service Fee Cost of Advertising                                             0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...              0.00   \n",
       "                  Subscription                                                    0.00   \n",
       "合计                                                                             1341.44   \n",
       "\n",
       "                                                                      shipping credits tax  \\\n",
       "Store type        description                                                                \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return                       0.00   \n",
       "                  FBA Inventory Reimbursement - Customer Service ...                  0.00   \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse                     0.00   \n",
       "                  FBA Inventory Reimbursement - General Adjustment                    0.00   \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                                       0.00   \n",
       "...                                                                                    ...   \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...                  0.00   \n",
       "      Service Fee Cost of Advertising                                                 0.00   \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...                  0.00   \n",
       "                  Subscription                                                        0.00   \n",
       "合计                                                                                   10.33   \n",
       "\n",
       "                                                                        total  \n",
       "Store type        description                                                  \n",
       "SC-US Adjustment  FBA Inventory Reimbursement - Customer Return         52.39  \n",
       "                  FBA Inventory Reimbursement - Customer Service ...    38.28  \n",
       "                  FBA Inventory Reimbursement - Damaged:Warehouse       97.70  \n",
       "                  FBA Inventory Reimbursement - General Adjustment     -15.20  \n",
       "      Debt        amzn1.cam.v1.sgid.20898641851                       5036.35  \n",
       "...                                                                       ...  \n",
       "YT-US Refund      UV Resin 1000g UV Epoxy Resin Crystal Clear Ult...   -26.39  \n",
       "      Service Fee Cost of Advertising                                 -783.14  \n",
       "                  Coupon Redemption Fee: Save 10% on Silicone Mol...    -6.00  \n",
       "                  Subscription                                         -12.85  \n",
       "合计                                                                   -4031.32  \n",
       "\n",
       "[146 rows x 17 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Regulatory Fee</th>\n",
       "      <th>Tax On Regulatory Fee</th>\n",
       "      <th>fba fees</th>\n",
       "      <th>gift wrap credits</th>\n",
       "      <th>giftwrap credits tax</th>\n",
       "      <th>marketplace withheld tax</th>\n",
       "      <th>other</th>\n",
       "      <th>other transaction fees</th>\n",
       "      <th>product sales</th>\n",
       "      <th>product sales tax</th>\n",
       "      <th>promotional rebates</th>\n",
       "      <th>promotional rebates tax</th>\n",
       "      <th>quantity</th>\n",
       "      <th>selling fees</th>\n",
       "      <th>shipping credits</th>\n",
       "      <th>shipping credits tax</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <th>type</th>\n",
       "      <th>description</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">SC-US</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Adjustment</th>\n",
       "      <th>FBA Inventory Reimbursement - Customer Return</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>52.39</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>52.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FBA Inventory Reimbursement - Customer Service Issue</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>38.28</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>38.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FBA Inventory Reimbursement - Damaged:Warehouse</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>97.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>97.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FBA Inventory Reimbursement - General Adjustment</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-15.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-15.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Debt</th>\n",
       "      <th>amzn1.cam.v1.sgid.20898641851</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5036.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5036.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">YT-US</th>\n",
       "      <th>Refund</th>\n",
       "      <th>UV Resin 1000g UV Epoxy Resin Crystal Clear Ultraviolet Curing Epoxy Resin Kit for DIY Jewelry Making, Craft Decoration - Hard UV Glue Solar Cure Sunl</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.47</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-29.99</td>\n",
       "      <td>-2.47</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-26.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Service Fee</th>\n",
       "      <th>Cost of Advertising</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-783.14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-783.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coupon Redemption Fee: Save 10% on Silicone Mold Making Kit</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-6.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subscription</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-12.85</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-12.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>合计</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-62974.91</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-4698.76</td>\n",
       "      <td>1396.16</td>\n",
       "      <td>-300.04</td>\n",
       "      <td>70599.24</td>\n",
       "      <td>4688.43</td>\n",
       "      <td>-4027.23</td>\n",
       "      <td>0</td>\n",
       "      <td>5869.0</td>\n",
       "      <td>-10065.98</td>\n",
       "      <td>1341.44</td>\n",
       "      <td>10.33</td>\n",
       "      <td>-4031.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>146 rows × 17 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "id": "b151f00e97ad3764",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-28T06:59:24.776230Z",
     "start_time": "2024-08-28T06:59:24.774674Z"
    }
   },
   "source": [],
   "outputs": [],
   "execution_count": 4
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
